﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Star.Tools.Excel.Model
{

    public class Workbook
    {
        public IEnumerable<Worksheet> Worksheets { get; private set; }
        public ICollection<NamedRange> Names { get; private set; }

        public Workbook(IEnumerable<Worksheet> worksheets)
        {
            Worksheets = worksheets;
            Names = new List<NamedRange>();
        }

        public DataSet AsDataSet(bool isFirstRowHeader)
        {
            var dataSet = new DataSet();
            foreach (Worksheet worksheet in Worksheets)
            {
                var dataTable = dataSet.Tables.Add(worksheet.Name);
                if (isFirstRowHeader)
                {
                    Row header = worksheet.Rows.FirstOrDefault();
                    if (header != null)
                        dataTable.Columns.AddRange(header.Cells.Select(cell => new DataColumn(cell.Value)).ToArray());
                }
                else
                    for (int i = 0; i < worksheet.ColumnCount; i++)
                        dataTable.Columns.Add();
                foreach (Row row in worksheet.Rows.Skip(isFirstRowHeader ? 1 : 0))
                    dataTable.Rows.Add(row.Cells.Select(cell => cell == null ? DBNull.Value : (object)cell.Value).ToArray());
            }
            return dataSet;
        }

        public static Workbook FromDataSet(DataSet dataSet, IFormatProvider format, bool columnsAsFirstRow)
        {
            var worksheets = new List<Worksheet>();
            foreach (DataTable dataTable in dataSet.Tables)
            {
                var rows = new List<Row>();
                if (columnsAsFirstRow)
                {
                    var cells = new List<Cell>();
                    foreach (DataColumn dataColumn in dataTable.Columns)
                        cells.Add(new Cell(0, cells.Count, dataColumn.ColumnName));
                    rows.Add(new Row(0, cells));
                }
                foreach (DataRow dataRow in dataTable.Rows)
                {
                    var cells = new List<Cell>();
                    foreach (object value in dataRow.ItemArray)
                    {
                        if (value == DBNull.Value)
                            cells.Add(null);
                        else
                        {
                            var formattable = value as IFormattable;
                            cells.Add(new Cell(rows.Count, cells.Count,
                                formattable == null ? value.ToString() : formattable.ToString(null, format)));
                        }
                    }
                    rows.Add(new Row(rows.Count, cells));
                }
                worksheets.Add(new Worksheet(dataTable.TableName, worksheets.Count, dataTable.Columns.Count, rows));
            }
            return new Workbook(worksheets);
        }
    }

    /// <summary>
    /// 单元格数据
    /// </summary>
    public class Cell
    {
        public int RowIndex { get; private set; }
        public int ColumnIndex { get; private set; }
        public string Value { get; private set; }
        public string DataFormat { get; private set; }

        public Cell(int rowIndex, int columnIndex, string value, string dataFormat = "")
        {
            RowIndex = rowIndex;
            ColumnIndex = columnIndex;
            Value = value;
            DataFormat = dataFormat;
        }
    }
    public class DataValidation
    {
        public Range Range { get; set; }
        public DataValidationType Type { get; set; }
        public ICollection<string> List { get; set; }
        public string Name { get; set; }
    }
    /// <summary>
    /// 单元格跨行
    /// </summary>
    public class Range
    {
        public int? RowStart { get; set; }
        public int? RowEnd { get; set; }
        public int? ColumnStart { get; set; }
        public int? ColumnEnd { get; set; }
        public string SheetName { get; set; }

        public Range() { }

        public Range(int? rowStart, int? rowEnd, int? columnStart, int? columnEnd, string sheetName = null)
        {
            RowStart = rowStart;
            RowEnd = rowEnd;
            ColumnStart = columnStart;
            ColumnEnd = columnEnd;
            SheetName = sheetName;
        }
    }

    /// <summary>
    /// 数据格式
    /// </summary>
    public enum DataValidationType
    {
        List,
        Formula
    }
    public class NamedRange
    {
        public string Name { get; set; }
        public Range Range { get; set; }
    }
   
    public class Row
    {
        public int Index { get; private set; }
        public IEnumerable<Cell> Cells { get; private set; }

        public Row(int index, IEnumerable<Cell> cells)
        {
            Index = index;
            Cells = cells;
        }
    }
    public class Worksheet
    {
        public string Name { get; private set; }
        public int Index { get; private set; }
        public int ColumnCount { get; private set; }
        public bool IsHidden { get; set; }
        public IEnumerable<Row> Rows { get; private set; }
        public ICollection<DataValidation> Validations { get; private set; }

        public Worksheet(string name, int index, int columnCount, IEnumerable<Row> rows)
        {
            Name = name;
            Index = index;
            ColumnCount = columnCount;
            Rows = rows;
            Validations = new List<DataValidation>();
        }
    }
}
